﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MDMAPI.Models;
using Dapper;

namespace MDMAPI.DAL
{
    public class UserInfoDAL
    {
        public IEnumerable<UserInfo> GetListAll()
        {
            using (var conn = SqlHelper.GetConnection())
            {
                var userList = conn.Query<UserInfo>("select * from sl_Userinfo");
                return userList;
            }
        }


        /// <summary>
        /// 根据电话查询UserInfo
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public IEnumerable<UserInfo> GetUserInfoByMobile(string mobile)
        {
            using (var conn = SqlHelper.GetConnection())
            {
                var userList = conn.Query<UserInfo>("select * from sl_Userinfo where mobile=@mobile", new { mobile = mobile });
                return userList;
            }
        }

        /// <summary>
        /// 根据电话更新用户信息
        /// </summary>
        /// <param name="userinfo"></param>
        /// <returns></returns>
        public int Update(UserInfo userinfo)
        {
            using (var conn = SqlHelper.GetConnection())
            {
                string updatesql = "update sl_userinfo set status=@status,username=@username,hotelcd=@hotelcd,jobgrade=@jobgrade, editdate=@editdate where Userid=@mobile";
                int result = conn.Execute(updatesql,
                    new
                    {
                        status = userinfo.Status,
                        username = userinfo.UserName,
                        hotelcd = userinfo.Hotelcd,
                        jobgrade = userinfo.JobGrade,
                        editdate = DateTime.Now,
                        mobile = userinfo.Mobile,
                    });
                return result;
            }
        }


        /// <summary>
        /// 增加一个新用户
        /// </summary>
        /// <param name="userinfo"></param>
        /// <returns></returns>
        public int Add(UserInfo userinfo)
        {
            using (var conn = SqlHelper.GetConnection())
            {
                string sql = "select * from sl_userinfo where UserID=@mobile ";
                var query= conn.Query(sql,new { mobile=userinfo.Mobile});
                string querymaxeidsql = "select max(Eid) as max from sl_userinfo "; //先找出最大的Eid，之后给他加上1
                if (query.Count() == 0)//假如数据库没有这个数据，插入
                {
                    var querymaxeid = conn.Query(querymaxeidsql);
                    long eid = querymaxeid.ToList()[0].max + 1; 
                    string salt = Guid.NewGuid().ToString().Replace("-", "").ToUpper(); //生成Guid
                    Random r = new Random();
                    string mingwenPwd = r.Next(10000000, 99999999).ToString();//生成8为数的随机密码          
                    string userPwd = StringExtension.HashSaltPassword(mingwenPwd, salt);//密码        
                    string insertSql = @"insert into sl_userinfo values
                             (@eid,@userid,@userPwd,@username,@salt,@depcode,@hotelcd,@jobgrade,@status,@createdate,@editdate,
                              @certtype,@certno,@birthday,@gender,@tel,@mobile,@email,@edituser) ";
                    int result = conn.Execute(insertSql, new
                    {
                        eid = eid,
                        userid = userinfo.Mobile,
                        userPwd = userPwd,
                        username = userinfo.UserName,
                        salt = salt,
                        depcode = "",
                        hotelcd = userinfo.Hotelcd,
                        jobgrade = userinfo.JobGrade,
                        status = 'N',
                        createdate = DateTime.Now,
                        editdate = DateTime.Now,
                        certtype = "",
                        certno = "",
                        birthday = "",
                        gender = 1,
                        tel = "",
                        mobile = userinfo.Mobile,
                        email = userinfo.Email,
                        edituser = "sys"
                    });               
                    #region 向临时表中插入记录,然后发邮件。
                    conn.Execute("insert into sl_UserinfoSendEmail values(@phone,@password,@name,@email)", new
                    {
                        phone = userinfo.Mobile,
                        password = mingwenPwd,
                        name = userinfo.UserName,
                        email = userinfo.Email
                    });
                    #endregion
                    return result; 
                }
                else //假如有这个电话，就不插入，并提示
                {
                    return -1;
                }
            
            }
        }


        public int Remove(string mobile)
        {
            using (var conn = SqlHelper.GetConnection())
            {
                string updatesql = "delete sl_userinfo where Userid=@mobile";
                int result = conn.Execute(updatesql,
                    new
                    {
                        mobile = mobile,
                    });
                return result;
            }
        }
    }
}